package com.xd.cloud.dao.device;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.xd.cloud.dao.BaseDao;
import com.xd.cloud.dao.Page;
import com.xd.cloud.entity.device.Sim;

/**   
 * 功能描述：sim信息Dao
 * @author: wangshanshan   
 * @date: 2017年12月12日 下午2:10:12 
 */
@Repository
public class SimDao extends BaseDao<Sim> {

	public Page getAllSim(int pageNo, int pageSize){
		String sql = "select s.id,s.sim_card,s.registration_time,s.status,s.install,d.device_id,s.company_id,c.name company_name,v.vehid from tb_sim s "
				+ "left join sys_company c on s.company_id = c.id "
				+ "left join tb_device_sim ds on s.id = ds.sim_id "
				+ "left join tb_device d on d.id = ds.device_id "
				+ "left join tb_vehicle v on d.device_id = v.devicecode order by s.id desc";
		return pagedSqlQuery(sql, pageNo, pageSize);
	}
	
	public Sim getSimById(int id) {
		String hql = "from Sim where id = ?";
		return get(hql, new Object[]{id});
	}

	public Sim getSimBySimCard(String sim_card) {
		String hql = "from Sim where sim_card = ?";
		return get(hql, new Object[]{sim_card});
	}
	
	public Object getSimObjectById(int id) {
		String sql = "select s.id,s.sim_card,s.registration_time,s.status,s.company_id,c.name company_name,s.remark\r\n" + 
				"from tb_sim s left join sys_company c\r\n" + 
				"on s.company_id = c.id\r\n" + 
				"where s.id = ?";
		return getsqlResult(sql, new Object[]{id});
	}
	
	public List<Object[]> getSimList() {
		String sql = "select s.id,s.sim_card,s.company_id,c.name company_name\r\n" + 
				"from tb_sim s left join sys_company c\r\n" + 
				"on s.company_id = c.id where s.status=1 and s.id not in (select sim_id from tb_device_sim) order by s.id desc\r\n" ;
		return findSql(sql, new Object[]{});
	}
	
	public void updateSimInstall(int id, int install) {
		String hql = "update Sim set install = ? where id = ? ";
		executeHql(hql, new Object[] {install, id});
	}
	
	public Sim getSim(String sim_card, int company_id) {
		String hql = "from Sim where sim_card = ? and company_id = ?";
		return get(hql, new Object[]{sim_card, company_id});
	}
	
	public Object getSimObjectByDeviceId(int device_id) {
		String sql = "select id,sim_card from tb_sim s where s.id in (select sim_id from tb_device_sim ds where ds.device_id = ?)";
		return getsqlResult(sql, new Object[]{device_id});
	}
}
